﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace forever.Gobal
{
    public class gencodeSharp
    {
        #region generate tables
        #region properties
        string DTO;
        string BLL;
        string DAL;
        string dataAccessLayer;
        string businessLogicLayer;
        string dataObjectLayer;
        private string tableName;
        private DataTable dt0;
        List<DataTable> dt1;
        List<DataTable> dt2;
        string nmspace;
        List<string> type;
        private List<string> whereType;
        List<string> description;
        List<string> nameMethod;
        List<string> returns = new List<string>();
        private string user;
        private string info;
        private int total1, total2;
        string dto1;
        string dto2;
        private string typeGenerate;
        DataTable dtPK;
        private string view;
        private string PresentationLayer;
        private string sproc;
        private bool chkLinkTable;
        private string connectTypeData;
        #endregion
        #region constructor
        public gencodeSharp(bool chklinktable, DataTable dtpk, string sproc, string typegenerate, string _tableName,
            string _user, string _info, string _view, string dto, string bll, string dal, string dataaccesslayer
            , string dataobjectlayer, string businesslogiclayer, string presentationlayer, string _nmspace, List<string> _type, List<string> wheretype,
            List<string> _description, List<string> namemethod, DataTable _dt0, List<DataTable> _dt1, List<DataTable> _dt2, string _cnData)
        {
            chkLinkTable = chklinktable;
            dtPK = dtpk;
            typeGenerate = typegenerate;
            tableName = Gobal.UplowerString(_tableName);
            this.sproc = sproc + tableName;
            user = _user;
            info = _info;
            connectTypeData = _cnData;
            DTO = tableName + dto;
            BLL = tableName + bll;
            DAL = tableName + dal;
            nmspace = _nmspace;
            view = tableName + _view;
            PresentationLayer = nmspace + "." + presentationlayer;
            this.dataAccessLayer = nmspace + "." + dataaccesslayer;
            this.dataObjectLayer = nmspace + "." + dataobjectlayer;
            this.businessLogicLayer = nmspace + "." + businesslogiclayer;
            dt0 = _dt0;
            dt1 = _dt1;
            dt2 = _dt2;
            type = _type;
            whereType = wheretype;
            description = _description;
            nameMethod = namemethod;
            dto1 = " _" + DTO.ToLower() + "1";
            dto2 = " _" + DTO.ToLower() + "2";
            foreach (string itemtype in type)
            {
                switch (itemtype)
                {
                    case "Select":
                    case "Page":
                        returns.Add("DataTable");
                        break;
                    case "Insert":
                    case "Delete":
                    case "Update":
                        returns.Add("bool");
                        break;
                }
            }
        }

        #endregion
        #region class
        public string GenerateSprocs()
        {
            string str = "";

            if (chkLinkTable)
            {
                str = "-- Authod by: " + user;
                str += Gobal.NewLinesAndTabs(1, 0) + "-- Info: " + info;
                str += Gobal.NewLinesAndTabs(1, 0) + "-- Date create: " + Gobal.getDate();
                str += Gobal.NewLinesAndTabs(2, 0) + "-- Danh sách Stored Sprocedures";
            }
            string select = "";
            string insert = "";
            string delete = "";
            string update = "";
            string pages = "";
            string item = "";
            int t = nameMethod.Count;
            for (int i = 0; i < t; i++)
            {
                item = "";
                if (chkLinkTable)
                {
                    item += GenerateCodeDescriptionSQLSERVER(i, 0);
                }
                item += GenerateContentMethod("Sproc", i);
                switch (type[i])
                {
                    case "Select":
                        select += item;
                        break;
                    case "Update":
                        update += item;
                        break;
                    case "Delete":
                        delete += item;
                        break;
                    case "Insert":
                        insert += item;
                        break;
                    case "Page":
                        pages += item;
                        break;
                }
            }
            str += GenerateCommentSQL("SELECT", select);
            str += GenerateCommentSQL("UPDATE", update);
            str += GenerateCommentSQL("DELETE", delete);
            str += GenerateCommentSQL("INSERT", insert);
            str += GenerateCommentSQL("PAGE", pages);
            return str;
        }
        public string GenerateCodeDescriptionSQLSERVER(int k, int tabs)
        {
            total2 = dt2[k].Rows.Count;
            total1 = dt1[k].Rows.Count;
            string str = "";
            //if (chkLinkTable)
            //{
            str += Gobal.NewLinesAndTabs(1, tabs) + "-- Description: " + description[k];
            if (type[k].Equals("Insert") || type[k].Equals("Update"))
            {
                for (int i = 0; i < total1 - 1; i++)
                {
                    str += Gobal.NewLinesAndTabs(1, tabs) + "-- " + type[k] + " column: " + dt1[k].Rows[i]["COLUMN_NAME"] + " " + dt1[k].Rows[i]["DATA_TYPE"];
                }
            }
            if (total2 > 0)
            {
                for (int i = 0; i < total2; i++)
                {
                    str += Gobal.NewLinesAndTabs(1, tabs) + "-- Where column: " + dt2[k].Rows[i]["COLUMN_NAME"] + " " + dt2[k].Rows[i]["DATA_TYPE"];
                }
            }
            if (type[k].Equals("Page"))
            {
                str += Gobal.NewLinesAndTabs(1, tabs) + "-- Value: currentPage int";
                str += Gobal.NewLinesAndTabs(1, tabs) + "-- Value: size int";
            }
            str += Gobal.NewLinesAndTabs(2, tabs) + "if exists (select * from sysobjects where id = object_id(N'[" + sproc + "_" + nameMethod[k] + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" +
            Gobal.NewLinesAndTabs(1, tabs + 1) + "drop procedure [" + sproc + "_" + nameMethod[k] + "]" +
            Gobal.NewLinesAndTabs(1, tabs - 1) + "GO";
            //}
            return str;
        }
        public string GenerateClassView()
        {
            string str = "" + GenerateInfoClass();
            str += Gobal.NewLinesAndTabs(2, 0) + "// Danh sách các phương thức được sử dụng cho phần hiển thị dữ liệu";
            str += Gobal.NewLinesAndTabs(1, 0) + "#region Methods & Functions";
            string select = "";
            string insert = "";
            string delete = "";
            string update = "";
            string pages = "";
            string item = "";
            int t = nameMethod.Count;
            for (int i = 0; i < t; i++)
            {
                item = "";
                item += GenerateCodeInfoMethod(0);
                item += GenerateCodeDescriptionMethod(i, 0);
                item += GenerateContentMethod("VIEW", i);
                switch (type[i])
                {
                    case "Select":
                        select += item;
                        break;
                    case "Update":
                        update += item;
                        break;
                    case "Delete":
                        delete += item;
                        break;
                    case "Insert":
                        insert += item;
                        break;
                    case "Page":
                        pages += item;
                        break;
                }
            }
            str += GenerateRegion("SELECT", select, 0);
            str += GenerateRegion("UPDATE", update, 0);
            str += GenerateRegion("DELETE", delete, 0);
            str += GenerateRegion("INSERT", insert, 0);
            str += GenerateRegion("PAGE", pages, 0);
            str += Gobal.NewLinesAndTabs(1, 0) + "#endregion";
            return str;
        }
        public string GenerateClassBLL()
        {
            string str = "" + GenerateInfoClass();
            str += Gobal.NewLinesAndTabs(2, 0) + "using System;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Collections.Generic;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Linq;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Data;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Text;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using " + dataAccessLayer + ";";
            str += Gobal.NewLinesAndTabs(1, 0) + "using " + dataObjectLayer + ";";
            str += Gobal.NewLinesAndTabs(1, 0) + "namespace " + businessLogicLayer;
            str += Gobal.NewLinesAndTabs(1, 0) + "{";
            str += Gobal.NewLinesAndTabs(1, 1) + "public class " + BLL;
            str += Gobal.NewLinesAndTabs(1, 1) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region Constructor";
            str += Gobal.NewLinesAndTabs(1, 2) + "public " + BLL + "()";
            str += Gobal.NewLinesAndTabs(1, 2) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "}";
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region Methods & Functions";
            string select = "";
            string insert = "";
            string delete = "";
            string update = "";
            string pages = "";
            string item = "";
            int t = nameMethod.Count;
            for (int i = 0; i < t; i++)
            {
                item = "";
                item += GenerateCodeInfoMethod(2);
                item += GenerateCodeDescriptionMethod(i, 2);
                item += GenerateContentMethod("BLL", i);
                switch (type[i])
                {
                    case "Select":
                        select += item;
                        break;
                    case "Update":
                        update += item;
                        break;
                    case "Delete":
                        delete += item;
                        break;
                    case "Insert":
                        insert += item;
                        break;
                    case "Page":
                        pages += item;
                        break;
                }
            }
            str += GenerateRegion("SELECT", select, 2);
            str += GenerateRegion("UPDATE", update, 2);
            str += GenerateRegion("DELETE", delete, 2);
            str += GenerateRegion("INSERT", insert, 2);
            str += GenerateRegion("PAGE", pages, 2);
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 1) + "}";
            str += Gobal.NewLinesAndTabs(1, 0) + "}";
            return str;
        }
        public string GenerateClassDAL()
        {
            string str = "" + GenerateInfoClass();
            str += Gobal.NewLinesAndTabs(2, 0) + "using System;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Collections.Generic;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Linq;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Data;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Text;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using forever.Gobal.CshapDataProvider;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using " + dataObjectLayer + ";";
            str += Gobal.NewLinesAndTabs(1, 0) + "namespace " + dataObjectLayer;
            str += Gobal.NewLinesAndTabs(1, 0) + "{";
            str += Gobal.NewLinesAndTabs(1, 1) + "public class " + DAL;
            str += Gobal.NewLinesAndTabs(1, 1) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region Constructor";
            str += Gobal.NewLinesAndTabs(1, 2) + "public " + DAL + "()";
            str += Gobal.NewLinesAndTabs(1, 2) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "}";
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region List methods";
            string select = "";
            string insert = "";
            string delete = "";
            string update = "";
            string pages = "";
            string item = "";
            int t = nameMethod.Count;
            for (int i = 0; i < t; i++)
            {
                item = "";
                item += GenerateCodeInfoMethod(2);
                item += GenerateCodeDescriptionMethod(i, 2);
                item += GenerateContentMethod("DAL", i);
                switch (type[i])
                {
                    case "Select":
                        select += item;
                        break;
                    case "Update":
                        update += item;
                        break;
                    case "Delete":
                        delete += item;
                        break;
                    case "Insert":
                        insert += item;
                        break;
                    case "Page":
                        pages += item;
                        break;
                }
            }
            str += GenerateRegion("SELECT", select, 2);
            str += GenerateRegion("UPDATE", update, 2);
            str += GenerateRegion("DELETE", delete, 2);
            str += GenerateRegion("INSERT", insert, 2);
            str += GenerateRegion("PAGE", pages, 2);
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 1) + "}";
            str += Gobal.NewLinesAndTabs(1, 0) + "}";
            return str;
        }
        public string GenerateClassDTO()
        {
            int total0 = dt0.Rows.Count - 1;
            string str = "" + GenerateInfoClass();
            str += Gobal.NewLinesAndTabs(2, 0) + "using System;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Collections.Generic;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Linq;";
            str += Gobal.NewLinesAndTabs(1, 0) + "using System.Text;";
            str += Gobal.NewLinesAndTabs(1, 0) + "namespace " + dataObjectLayer;
            str += Gobal.NewLinesAndTabs(1, 0) + "{";
            str += Gobal.NewLinesAndTabs(1, 1) + "public class " + DTO;
            str += Gobal.NewLinesAndTabs(1, 1) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region Constructor";
            str += Gobal.NewLinesAndTabs(1, 2) + "public " + DTO + "()";
            str += Gobal.NewLinesAndTabs(1, 2) + "{";
            str += Gobal.NewLinesAndTabs(1, 2) + "}";
            str += Gobal.NewLinesAndTabs(1, 2) + "public " + DTO + "(";
            for (int i = 0; i < total0; i++)
            {
                str += Gobal.ToClrType(dt0.Rows[i]["DATA_TYPE"].ToString()) + " " + dt0.Rows[i]["COLUMN_NAME"] + ", ";
            }
            str += Gobal.ToClrType(dt0.Rows[total0]["DATA_TYPE"].ToString()) + " " + dt0.Rows[total0]["COLUMN_NAME"] + ")";
            str += Gobal.NewLinesAndTabs(1, 2) + "{";
            total0++;
            for (int i = 0; i < total0; i++)
            {
                str += Gobal.NewLinesAndTabs(1, 3) + "this._" + dt0.Rows[i]["COLUMN_NAME"] + " = " + dt0.Rows[i]["COLUMN_NAME"] + ";";
            }
            str += Gobal.NewLinesAndTabs(1, 2) + "}";
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 2) + "#region Properties";
            for (int i = 0; i < total0; i++)
            {
                str += GenerateProperty(dt0.Rows[i]["COLUMN_NAME"].ToString(), dt0.Rows[i]["DATA_TYPE"].ToString());
            }
            str += Gobal.NewLinesAndTabs(1, 2) + "#endregion";
            str += Gobal.NewLinesAndTabs(1, 1) + "}";
            str += Gobal.NewLinesAndTabs(1, 0) + "}";
            return str;
        }
        public string GenerateInfoClass()
        {
            string str = "";
            if (chkLinkTable)
            {
                str = "/// Authod by: " + user;
                str += Gobal.NewLinesAndTabs(1, 0) + "/// Info: " + info;
                str += Gobal.NewLinesAndTabs(1, 0) + "/// Date create: " + Gobal.getDate();
            }
            return str;
        }
        public string GenerateRegion(string loai, string text, int tabs)
        {
            string str = ""; str += Gobal.NewLinesAndTabs(1, tabs) + "#region " + loai;
            str += (text.Equals("") ? Gobal.NewLinesAndTabs(1, tabs) + "//Không có dữ liệu;" : text);
            str += Gobal.NewLinesAndTabs(1, tabs) + "#endregion";
            return str;
        }
        public string GenerateCommentSQL(string loai, string text)
        {
            string str = "";
            str += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ " + loai + " ------------------------------------ ";
            str += (text.Equals("") ? Gobal.NewLinesAndTabs(1, 0) + "--Không có dữ liệu;" : text);
            str += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ END " + loai + " ------------------------------------ ";
            return str;
        }
        #endregion
        #region method & sprocedures three layer
        public string GenerateProperty(string name, string type)
        {
            string str = "";
            str += Gobal.NewLinesAndTabs(1, 2) + "private " + Gobal.ToClrType(type) + " _" + name + ";";
            str += Gobal.NewLinesAndTabs(1, 2) + "///<summary>";
            str += Gobal.NewLinesAndTabs(1, 2) + "/// Get or set " + Gobal.ToClrType(type) + " " + name;
            str += Gobal.NewLinesAndTabs(1, 2) + "///</summary>";

            str += Gobal.NewLinesAndTabs(1, 2) + "public " + Gobal.ToClrType(type) + " " + Gobal.UplowerString(name);
            str += Gobal.NewLinesAndTabs(1, 2) + "{";
            str += Gobal.NewLinesAndTabs(1, 3) + "get { return _" + name + "; }";
            str += Gobal.NewLinesAndTabs(1, 3) + "set { _" + name + " = value; }";
            str += Gobal.NewLinesAndTabs(1, 2) + "}";
            return str;
        }
        public string GenerateContentMethod(string loai, int k)
        {
            total1 = dt1[k].Rows.Count;
            total2 = dt2[k].Rows.Count;
            string str = "";
            switch (loai)
            {
                #region taks: view, bll, dal
                case "VIEW":
                    str += Gobal.NewLinesAndTabs(1, 0) + "public " + returns[k] + " " + nameMethod[k] + "(";
                    str += GenerateHeaderMethodByValues(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, 0) + "{";
                    str += Gobal.NewLinesAndTabs(1, 1) + BLL + " _" + BLL.ToLower() + "= new " + BLL + "();";
                    str += Gobal.NewLinesAndTabs(1, 1) + "return _" + BLL.ToLower() + "." + nameMethod[k] + "(";
                    str += GenerateButtonMethodByValues(k);
                    str += ");";
                    str += Gobal.NewLinesAndTabs(1, 0) + "}";
                    break;
                case "BLL":
                    str += Gobal.NewLinesAndTabs(1, 2) + "public " + returns[k] + " " + nameMethod[k] + "(";
                    str += GenerateHeaderMethodByValues(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, 2) + "{";
                    str += Gobal.NewLinesAndTabs(1, 3) + DAL + " _" + DAL.ToLower() + "= new " + DAL + "();";
                    str += Gobal.NewLinesAndTabs(1, 3) + "return _" + DAL.ToLower() + "." + nameMethod[k] + "(";
                    str += GenerateButtonMethodByValues(k);
                    str += ");";
                    str += Gobal.NewLinesAndTabs(1, 2) + "}";
                    break;
                case "DAL":
                    str += Gobal.NewLinesAndTabs(1, 2) + "public " + returns[k] + " " + nameMethod[k] + "(";
                    str += GenerateHeaderMethodByValues(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, 2) + "{";
                    switch (typeGenerate)
                    {
                        case "not layer":
                            break;
                        case "forever libary":
                            str += Gobal.NewLinesAndTabs(1, 3) + "SqlServerDataProvider provider = new SqlServerDataProvider();";
                            str += Gobal.NewLinesAndTabs(1, 3) + "string sql = \"" + sproc + "_" + nameMethod[k] + "\";";
                            str += "return provider." + (returns[k].Equals("bool") ? "ExecuteNonQuery" : "GetRecordSet") + "(sql,";
                            //str += Gobal.NewLinesAndTabs(1, 3) + "DataTable dt = provider." + (returns[k].Equals("bool") ? "ExecuteNonQuery" : "GetRecordSet") + "(sql,";
                            if (type[k].Equals("Insert") || type[k].Equals("Update") || total2 > 0)
                            {
                                str += " new SqlServerDataProvider.DatabaseParamCls[] { ";
                                if (type[k].Equals("Insert") || type[k].Equals("Update"))
                                {
                                    for (int i = 0; i < total1; i++)
                                    {
                                        str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@" + dt1[k].Rows[i]["COLUMN_NAME"] + "\", " + dt1[k].Rows[i]["COLUMN_NAME"] + "),";
                                    }
                                    if (type[k].Equals("Insert"))
                                    {
                                        str = str.Remove(str.Length - 1);
                                    }
                                }
                                if (total2 > 0)
                                {
                                    for (int i = 0; i < total2 - 1; i++)
                                    {
                                        str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@w" + dt2[k].Rows[i]["COLUMN_NAME"] + "\", w" + dt2[k].Rows[i]["COLUMN_NAME"] + "),";
                                    } str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + "\", w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + ")";
                                    if (type[k].Equals("Page"))
                                    {
                                        str += "," + Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@currentPage\", currentPage),";
                                        str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@size\", size)";
                                    }
                                }
                                str += Gobal.NewLinesAndTabs(1, 3) + "}, ";
                            }
                            else if (type[k].Equals("Page"))
                            {
                                str += " new SqlServerDataProvider.DatabaseParamCls[] { ";
                                str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@currentPage\", currentPage),";
                                str += Gobal.NewLinesAndTabs(1, 4) + " new SqlServerDataProvider.DatabaseParamCls(\"@size\", size)";
                                str += Gobal.NewLinesAndTabs(1, 3) + "}, ";
                            }
                            str += " CommandType.StoredProcedure);";
                            break;
                    }
                    //str += Gobal.NewLinesAndTabs(1, 3) + DAL + " _" + DAL.ToLower() + "= new " + DAL + "();";
                    //str += Gobal.NewLinesAndTabs(1, 3) + "return _" + DAL.ToLower() + "." + nameMethod[k] + "(";
                    //str += GenerateButtonMethodByDTO(k);
                    //str += Gobal.NewLinesAndTabs(1, 3) + "return " + (returns[k].Equals("bool") ? "dt.Rows.Count >= 0 ? true : false" : "dt") + ";";
                    str += Gobal.NewLinesAndTabs(1, 2) + "}";
                    break;
                #endregion
                #region sproc
                case "Sproc":
                    str = Gobal.NewLinesAndTabs(1, 0) + "CREATE PROCEDURE [dbo].[" + sproc + "_" + nameMethod[k] + "]";
                    if (type[k].Equals("Insert") || type[k].Equals("Update"))
                    {
                        for (int i = 0; i < total1; i++)
                        {
                            str += Gobal.NewLinesAndTabs(1, 1) + "@" + dt1[k].Rows[i]["COLUMN_NAME"] + " "
                                + dt1[k].Rows[i]["DATA_TYPE"]
                                + (dt1[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("0") ? "," : (int.Parse(dt1[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString()) > 10000 ? "," : "(" + dt1[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"] + "),"));
                        }
                        if (type[k].Equals("Insert"))
                        {
                            str = str.Remove(str.Length - 1);
                        }
                    }
                    if (total2 > 0)
                    {
                        for (int i = 0; i < total2 - 1; i++)
                        {
                            str += Gobal.NewLinesAndTabs(1, 1) + "@w" + dt2[k].Rows[i]["COLUMN_NAME"] + " "
                                + dt2[k].Rows[i]["DATA_TYPE"]
                                + (dt2[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("0") ? "," : (int.Parse(dt2[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString()) > 10000 ? "," : "(" + dt2[k].Rows[i]["CHARACTER_MAXIMUM_LENGTH"] + "),"));
                        } str += Gobal.NewLinesAndTabs(1, 1) + "@w"
                            + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + " "
                            + dt2[k].Rows[total2 - 1]["DATA_TYPE"]
                            + (dt2[k].Rows[total2 - 1]["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("0") ? "" : (int.Parse(dt2[k].Rows[total2 - 1]["CHARACTER_MAXIMUM_LENGTH"].ToString()) > 10000 ? "," : "(" + dt2[k].Rows[total2 - 1]["CHARACTER_MAXIMUM_LENGTH"] + ")"));

                        if (type[k].Equals("Page"))
                        {
                            str += "," + Gobal.NewLinesAndTabs(1, 1) + "@currentPage int,";
                            str += Gobal.NewLinesAndTabs(1, 1) + "@pageSize int";
                        }
                    }
                    else if (type[k].Equals("Page"))
                    {
                        str += Gobal.NewLinesAndTabs(1, 1) + "@currentPage int,";
                        str += Gobal.NewLinesAndTabs(1, 1) + "@pageSize int";
                    }
                    str += Gobal.NewLinesAndTabs(1, 0) + "AS" + Gobal.NewLinesAndTabs(1, 0) + "BEGIN";
                    switch (type[k])
                    {
                        case "Select":
                            str += Gobal.NewLinesAndTabs(1, 1) + "SELECT ";
                            for (int i = 0; i < total1 - 1; i++)
                            {
                                str += "[" + dt1[k].Rows[i]["COLUMN_NAME"] + "] ," + Gobal.NewLinesAndTabs(1, 2);
                            }
                            str += "[" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + "]";
                            str += Gobal.NewLinesAndTabs(1, 1) + "FROM [dbo].[" + tableName + "]";
                            if (total2 > 0)
                            {
                                str += Gobal.NewLinesAndTabs(1, 1) + "WHERE ";
                                for (int i = 0; i < total2 - 1; i++)
                                {
                                    str += "[" + dt2[k].Rows[i]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[i]["COLUMN_NAME"] + " " + whereType[k] + " ";
                                }
                                str += "[" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                            }
                            break;
                        case "Update":
                            str += Gobal.NewLinesAndTabs(1, 1) + "UPDATE [dbo].[" + tableName + "] SET";
                            for (int i = 0; i < total1 - 1; i++)
                            {
                                str += "[" + dt1[k].Rows[i]["COLUMN_NAME"] + "] =@" + dt1[k].Rows[i]["COLUMN_NAME"] + "," + Gobal.NewLinesAndTabs(1, 2);
                            }
                            str += "[" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + "] =@" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"];
                            if (total2 > 0)
                            {
                                str += Gobal.NewLinesAndTabs(1, 1) + "WHERE ";
                                for (int i = 0; i < total2 - 1; i++)
                                {
                                    str += "[" + dt2[k].Rows[i]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[i]["COLUMN_NAME"] + " " + whereType[k] + " ";
                                }
                                str += "[" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                            }
                            break;

                        case "Delete":
                            str += Gobal.NewLinesAndTabs(1, 1) + "DELETE FROM [dbo].[" + tableName + "]";
                            if (total2 > 0)
                            {
                                str += Gobal.NewLinesAndTabs(1, 1) + "WHERE ";
                                for (int i = 0; i < total2 - 1; i++)
                                {
                                    str += "[" + dt2[k].Rows[i]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[i]["COLUMN_NAME"] + " " + whereType[k] + " ";
                                }
                                str += "[" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                            }
                            break;

                        case "Insert":
                            str += Gobal.NewLinesAndTabs(1, 1) + "INSERT INTO [dbo].[" + tableName + "](";
                            for (int i = 0; i < total1 - 1; i++)
                            {
                                str += "[" + dt1[k].Rows[i]["COLUMN_NAME"] + "] ," + Gobal.NewLinesAndTabs(1, 2);
                            }
                            str += "[" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + "])";
                            str += Gobal.NewLinesAndTabs(1, 1) + "VALUES (";
                            for (int i = 0; i < total1 - 1; i++)
                            {
                                str += "@" + dt1[k].Rows[i]["COLUMN_NAME"] + " ," + Gobal.NewLinesAndTabs(1, 2);
                            }
                            str += "@" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + ")";
                            break;

                        case "Page":
                            str += Gobal.NewLinesAndTabs(1, 1) + "WITH tbpage_Paging AS";
                            str += Gobal.NewLinesAndTabs(1, 1) + "(";
                            str += Gobal.NewLinesAndTabs(1, 2) + "SELECT ROW_NUMBER()";
                            str += Gobal.NewLinesAndTabs(1, 2) + "OVER(ORDER BY ";
                            if (dtPK.Rows.Count > 0)
                            {
                                for (int i = 0; i < dtPK.Rows.Count - 1; i++)
                                {
                                    str += dtPK.Rows[i]["COLUMN_NAME"] + "," + Gobal.NewLinesAndTabs(1, 3);
                                } str += dtPK.Rows[dtPK.Rows.Count - 1]["COLUMN_NAME"];
                            }
                            else
                            {
                                str += dt1[k].Rows[0]["COLUMN_NAME"];
                            }
                            str += Gobal.NewLinesAndTabs(1, 2) + ") AS RowNum, ";
                            for (int i = 0; i < total1 - 1; i++)
                            {
                                str += "[" + dt1[k].Rows[i]["COLUMN_NAME"] + "] , ";
                            }
                            str += "[" + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + "]";
                            str += Gobal.NewLinesAndTabs(1, 2) + "FROM [dbo].[" + tableName + "]";
                            if (total2 > 0)
                            {
                                str += Gobal.NewLinesAndTabs(1, 2) + "WHERE ";
                                for (int i = 0; i < total2 - 1; i++)
                                {
                                    str += "[" + dt2[k].Rows[i]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[i]["COLUMN_NAME"] + " " + whereType[k] + " ";
                                }
                                str += "[" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"] + "] = @w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                            }
                            str += Gobal.NewLinesAndTabs(1, 1) + ")";
                            str += Gobal.NewLinesAndTabs(1, 1) + "SELECT * FROM tbpage_Paging";
                            str += Gobal.NewLinesAndTabs(1, 1) + "WHERE RowNum BETWEEN (@currentPage - 1) * @pageSize + 1 AND @currentPage * @pageSize";
                            break;

                    }
                    str += Gobal.NewLinesAndTabs(1, 0) + "END";
                    str += Gobal.NewLinesAndTabs(1, 0) + "GO";
                    break;
                #endregion
            }
            str += Gobal.NewLinesAndTabs(1, 0);
            return str;
        }
        public string GenerateHeaderMethodByValues(int k)
        {
            total2 = dt2[k].Rows.Count;
            total1 = dt1[k].Rows.Count;
            string str = "";
            if (type[k].Equals("Insert") || type[k].Equals("Update"))
            {
                for (int i = 0; i < total1 - 1; i++)
                {
                    str += Gobal.ToClrType(dt1[k].Rows[i]["DATA_TYPE"].ToString()) + " " + dt1[k].Rows[i]["COLUMN_NAME"] + ", ";
                }
                str += Gobal.ToClrType(dt1[k].Rows[total1 - 1]["DATA_TYPE"].ToString()) + " " + dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + (type[k].Equals("Insert") ? "" : ", ");
            }
            if (total2 > 0)
            {
                for (int i = 0; i < total2 - 1; i++)
                {
                    str += Gobal.ToClrType(dt2[k].Rows[i]["DATA_TYPE"].ToString()) + " w" + dt2[k].Rows[i]["COLUMN_NAME"] + ", ";
                }
                str += Gobal.ToClrType(dt2[k].Rows[total2 - 1]["DATA_TYPE"].ToString()) + " w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                if (type[k].Equals("Page"))
                {
                    str += ", int currentPage, int size";
                }
            }
            else if (type[k].Equals("Page"))
            {
                str += "int currentPage, int size";
            }
            return str;
        }
        //public string GenerateMethodByValues(int k)
        //{
        //    total2 = dt2[k].Rows.Count;
        //    string str = "";
        //    if (type[k].Equals("Insert") || type[k].Equals("Update"))
        //    {
        //        for (int i = 0; i < total1 - 1; i++)
        //        {
        //            str += dt1[k].Rows[i]["COLUMN_NAME"] + ", ";
        //        }
        //        str += dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + (type[k].Equals("Insert") ? "" : ", ");
        //    }
        //    if (total2 > 0)
        //    {
        //        for (int i = 0; i < total2 - 1; i++)
        //        {
        //            str += " w" + dt2[k].Rows[i]["COLUMN_NAME"] + ", ";
        //        }
        //        str += " w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
        //        if (type[k].Equals("Page"))
        //        {
        //            str += ", currentPage, size";
        //        }
        //    }
        //    else if (type[k].Equals("Page"))
        //    {
        //        str += "currentPage, size";
        //    }
        //    return str;
        //}
        public string GenerateButtonMethodByValues(int k)
        {
            total2 = dt2[k].Rows.Count;
            total1 = dt1[k].Rows.Count;
            string str = "";
            if (type[k].Equals("Insert") || type[k].Equals("Update"))
            {
                for (int i = 0; i < total1 - 1; i++)
                {
                    str += dt1[k].Rows[i]["COLUMN_NAME"] + ", ";
                }
                str += dt1[k].Rows[total1 - 1]["COLUMN_NAME"] + (type[k].Equals("Insert") ? "" : ", ");
            }
            if (total2 > 0)
            {
                for (int i = 0; i < total2 - 1; i++)
                {
                    str += "w" + dt2[k].Rows[i]["COLUMN_NAME"] + ", ";
                }
                str += "w" + dt2[k].Rows[total2 - 1]["COLUMN_NAME"];
                if (type[k].Equals("Page"))
                {
                    str += ", currentPage, size";
                }
            }
            else if (type[k].Equals("Page"))
            {
                str += "currentPage, size";
            }
            return str;
        }
        public string GenerateHeaderMethodByDTO(int k)
        {
            total2 = dt2[k].Rows.Count;
            string str = "";
            if (type[k].Equals("Insert") || type[k].Equals("Update"))
            {
                str += DTO + " " + dto1;
            }
            if (total2 > 0)
            {
                str += DTO + " " + dto2;
                if (type[k].Equals("Pages"))
                {
                    str += ", int currentPage, int size";
                }
            }
            else if (type[k].Equals("Pages"))
            {
                str += "int currentPage, int size";
            }
            return str;
        }
        public string GenerateButtonMethodByDTO(int k)
        {
            total2 = dt2[k].Rows.Count;
            string str = "";
            if (type[k].Equals("Insert") || type[k].Equals("Update"))
            {
                str += dto1 + ", ";
            }
            if (total2 > 0)
            {
                str += dto2;
                if (type[k].Equals("Pages"))
                {
                    str += ", currentPage, size";
                }
            }
            else if (type[k].Equals("Pages"))
            {
                str += "currentPage, size";
            }
            return str;
        }
        public string GenerateConverFromValueFormDTO(DataTable dt, string dto, int k)
        {
            string str = "";
            if (dt.Rows.Count > 0)
            {
                str += Gobal.NewLinesAndTabs(1, 3) + DTO + " " + dto + " = new " + DTO + "();";
                for (int i = 0; i < dt.Rows.Count - 1; i++)
                {
                    str += Gobal.NewLinesAndTabs(1, 3) + dto + "." + Gobal.UplowerString(dt.Rows[i]["COLUMN_NAME"].ToString()) + " = " + (type[k].Equals("Insert") || type[k].Equals("Update") ? "w" : "") + dt.Rows[i]["COLUMN_NAME"] + ";";
                }
                str += Gobal.NewLinesAndTabs(1, 3) + dto + "." + Gobal.UplowerString(dt.Rows[dt.Rows.Count - 1]["COLUMN_NAME"].ToString()) + " = " + (type[k].Equals("Insert") || type[k].Equals("Update") ? "w" : "") + dt.Rows[dt.Rows.Count - 1]["COLUMN_NAME"] + ";";
            }
            return str;
        }
        public string GenerateCodeInfoMethod(int tabs)
        {
            string str = "";
            if (chkLinkTable)
            {
                str = Gobal.NewLinesAndTabs(1, tabs) + "/// Create by: " + user + " - " + Gobal.getDate();
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// Modified by: ? - ?: Why modified?";
            }
            return str;
        }
        public string GenerateCodeDescriptionMethod(int k, int tabs)
        {
            total2 = dt2[k].Rows.Count;
            total1 = dt1[k].Rows.Count;
            string str = "";
            if (chkLinkTable)
            {
                str = Gobal.NewLinesAndTabs(1, tabs) + "/// <summary>"
                             + Gobal.NewLinesAndTabs(1, tabs) + "/// " + description[k]
                             + Gobal.NewLinesAndTabs(1, tabs) + "/// </summary>";
                if (type[k].Equals("Insert") || type[k].Equals("Update"))
                {
                    for (int i = 0; i < total1; i++)
                    {
                        str += Gobal.NewLinesAndTabs(1, tabs) + "/// <param name=\"" + dt1[k].Rows[i]["COLUMN_NAME"] + "\"> " + type[k] + " column: " +
                                Gobal.ToClrType(dt1[k].Rows[i]["DATA_TYPE"].ToString()) + " " + dt1[k].Rows[i]["COLUMN_NAME"] + " </param>";
                    }
                }
                if (total2 > 0)
                {
                    for (int i = 0; i < total2; i++)
                    {
                        str += Gobal.NewLinesAndTabs(1, tabs) + "/// <param name=\"" + dt2[k].Rows[i]["COLUMN_NAME"] + "\"> Where column: " +
                            Gobal.ToClrType(dt2[k].Rows[i]["DATA_TYPE"].ToString()) + " " + dt2[k].Rows[i]["COLUMN_NAME"] + " </param>";
                    }
                }
                if (type[k].Equals("Pages"))
                {
                    str += Gobal.NewLinesAndTabs(1, tabs) + "/// <param name=\"currentPage\"> int currentPage </param>";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "/// <param name=\"size\"> int size </param>";
                }
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// <return> " + returns[k] + " </return>";
            }
            return str;
        }
        #endregion
        #endregion
        #region generate sproc
        #region properties
        private List<DataTable> dtParamester;
        private DataTable namesproc;
        private string db;
        private List<string> returnSproc;
        private List<string> descriptionSproc;
        private List<string> nameMethodsSproc;
        #endregion
        public gencodeSharp(bool _chklinktable, string typegenerate,
            string _user, string _info, string _view, string bll, string dal, string dataaccesslayer
            , string dataobjectlayer, string businesslogiclayer, string presentationlayer, string _nmspace, List<DataTable> _dtParamester, DataTable _namesproc,
           string _db, string _cnData, List<string> _returnSproc, List<string> _descriptionSproc, List<string> nameMethods, string classcommon)
        {
            this.nameMethodsSproc = nameMethods;
            chkLinkTable = _chklinktable;
            typeGenerate = typegenerate;
            user = _user;
            info = _info;
            tableName = classcommon;
            connectTypeData = _cnData;
            descriptionSproc = _descriptionSproc;
            BLL = tableName + bll;
            DAL = tableName + dal;
            nmspace = _nmspace;
            view = tableName + _view;
            PresentationLayer = nmspace + "." + presentationlayer;
            this.dataAccessLayer = nmspace + "." + dataaccesslayer;
            this.dataObjectLayer = nmspace + "." + dataobjectlayer;
            this.businessLogicLayer = nmspace + "." + businesslogiclayer;
            this.dtParamester = _dtParamester;
            this.namesproc = _namesproc;
            this.db = _db;
            this.returnSproc = _returnSproc;
        }
        public string GenerateButtonMethodByValuesBySproc(int k)
        {
            int total1 = dtParamester[k].Rows.Count;
            string str = "";
            for (int i = 0; i < total1 - 1; i++)
            {
                str += getParamesterName(dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString()) + ", ";
            }
            str += getParamesterName(dtParamester[k].Rows[total1 - 1]["PARAMETER_NAME"].ToString());
            return str;
        }
        public string GenerateHeaderMethodByValuesBySproc(int k)
        {
            int total1 = dtParamester[k].Rows.Count;
            string str = "";
            for (int i = 0; i < total1 - 1; i++)
            {
                str += Gobal.ToClrType(dtParamester[k].Rows[i]["DATA_TYPE"].ToString()) + " " + getParamesterName(dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString()) + ", ";
            }
            str += Gobal.ToClrType(dtParamester[k].Rows[total1 - 1]["DATA_TYPE"].ToString()) + " " + getParamesterName(dtParamester[k].Rows[total1 - 1]["PARAMETER_NAME"].ToString());
            return str;
        }
        string getParamesterName(string a)
        {
            return a.Remove(0, 1);
        }
        public string GenerateCodeDescriptionMethodSproc(int k, int tabs)
        {
            string str = "";
            if (chkLinkTable)
            {
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// Create by: " + user + " - " + Gobal.getDate();
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// Modified by: ? - ?: Why modified?";
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// <summary>"
                             + Gobal.NewLinesAndTabs(1, tabs) + "/// " + nameMethodsSproc[k]
                             + Gobal.NewLinesAndTabs(1, tabs) + "/// " + descriptionSproc[k]
                + Gobal.NewLinesAndTabs(1, tabs) + "/// </summary>";
                for (int i = 0; i < dtParamester[k].Rows.Count; i++)
                {
                    str += Gobal.NewLinesAndTabs(1, tabs) + "/// <param name=\"" + getParamesterName(dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString()) + "\"> Column: " +
                            Gobal.ToClrType(dtParamester[k].Rows[i]["DATA_TYPE"].ToString()) + " " + getParamesterName(dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString()) + " </param>";
                }
                str += Gobal.NewLinesAndTabs(1, tabs) + "/// <return> " + returnSproc[k] + " </return>";
            }
            return str;
        }
        public string GenerateMethodsInfoListSproc(int tabs, string type)
        {
            string str = "";
            if (chkLinkTable)
            {
                switch (type)
                {
                    case "methods":
                        str = "/// Authod by: " + user;
                        str += Gobal.NewLinesAndTabs(1, tabs) + "/// Info: " + info;
                        str += Gobal.NewLinesAndTabs(1, tabs) + "/// Date create: " + Gobal.getDate();
                        break;
                    case "sproc":
                        str = "-- Authod by: " + user;
                        str += Gobal.NewLinesAndTabs(1, tabs) + "-- Info: " + info;
                        str += Gobal.NewLinesAndTabs(1, tabs) + "-- Date create: " + Gobal.getDate();
                        break;
                }
            }
            return str;
        }
        public string GenerateRegionMethodSproc(string text, string ten, int tabs)
        {
            string str = "";
            str += Gobal.NewLinesAndTabs(1, tabs) + "#region " + ten;
            str += text;
            str += Gobal.NewLinesAndTabs(1, tabs) + "#endregion";
            return text == "" ? "" : str;
        }
        public string GenerateCommentSQLSproc(string text, int k)
        {
            string str = "";
            str += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ " + namesproc.Rows[k]["SPECIFIC_NAME"].ToString() + " ------------------------------------ ";
            str += Gobal.NewLinesAndTabs(1, 0) + "--" + descriptionSproc[k];
            str += Gobal.NewLinesAndTabs(1, 0) + text;
            str += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ END " + namesproc.Rows[k]["SPECIFIC_NAME"].ToString() + " ------------------------------------ ";
            return text == "" ? "" : str;
        }
        public List<string> GenerateListMethodBySproc()
        {
            List<string> list = new List<string>();
            string view = "", sproc = "", dal = "", bll = "";
            int t = namesproc.Rows.Count;
            view = dal = bll = GenerateMethodsInfoListSproc(0, "methods"); sproc += GenerateMethodsInfoListSproc(0, "sproc");
            if (t == 1)
            {
                view += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(0, 0) + GenerateContentMethodBySproc("VIEW", 0, 0), nameMethodsSproc[0], 0);
                dal += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(0, 0) + GenerateContentMethodBySproc("DAL", 0, 0), nameMethodsSproc[0], 0);
                bll += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(0, 0) + GenerateContentMethodBySproc("BLL", 0, 0), nameMethodsSproc[0], 0);
                sproc += GenerateCommentSQLSproc(GenerateContentMethodBySproc("Sproc", 0, 0), 0);
            }
            else
            {
                sproc += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ Creation date List stored sprocedures " + DateTime.Now + " ------------------------------------ ";
                for (int i = 0; i < t; i++)
                {
                    view += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(i, 0) + GenerateContentMethodBySproc("VIEW", i, 0), nameMethodsSproc[i], 0);
                    dal += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(i, 0) + GenerateContentMethodBySproc("DAL", i, 0), nameMethodsSproc[i], 0);
                    bll += GenerateRegionMethodSproc(GenerateCodeDescriptionMethodSproc(i, 0) + GenerateContentMethodBySproc("BLL", i, 0), nameMethodsSproc[i], 0);
                    sproc += GenerateCommentSQLSproc(GenerateContentMethodBySproc("Sproc", i, 0), i);
                }
                view += GenerateRegionMethodSproc(view, "Creation date List methods " + Gobal.getDate(), 0);
                dal += GenerateRegionMethodSproc(dal, "Creation date List methods " + Gobal.getDate(), 0);
                bll += GenerateRegionMethodSproc(bll, "Creation date List methods " + Gobal.getDate(), 0);
                sproc += Gobal.NewLinesAndTabs(1, 0) + "------------------------------------ End creation date List stored sprocedures " + DateTime.Now + " ------------------------------------ ";
            }
            list.Add(view);
            list.Add(dal);
            list.Add(bll);
            list.Add(sproc);
            return list;
        }
        public string GenerateContentMethodBySproc(string loai, int k, int tabs)
        {
            string str = "";
            switch (loai)
            {
                case "VIEW":
                    str += Gobal.NewLinesAndTabs(1, tabs) + "public " + returnSproc[k] + " " + nameMethodsSproc[k] + "(";
                    str += GenerateHeaderMethodByValuesBySproc(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "{";
                    str += Gobal.NewLinesAndTabs(1, tabs + 1) + BLL + " _" + BLL.ToLower() + "= new " + BLL + "();";
                    str += Gobal.NewLinesAndTabs(1, tabs + 1) + "return _" + BLL.ToLower() + "." + nameMethodsSproc[k] + "(";
                    str += GenerateButtonMethodByValuesBySproc(k);
                    str += ");";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "}";
                    break;
                case "BLL":
                    str += Gobal.NewLinesAndTabs(1, tabs) + "public " + returnSproc[k] + " " + nameMethodsSproc[k] + "(";
                    str += GenerateHeaderMethodByValuesBySproc(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "{";
                    str += Gobal.NewLinesAndTabs(1, tabs + 1) + DAL + " _" + DAL.ToLower() + "= new " + DAL + "();";
                    str += Gobal.NewLinesAndTabs(1, tabs + 1) + "return _" + DAL.ToLower() + "." + nameMethodsSproc[k] + "(";
                    str += GenerateButtonMethodByValuesBySproc(k);
                    str += ");";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "}";
                    break;
                case "DAL":
                    str += Gobal.NewLinesAndTabs(1, tabs) + "public " + returnSproc[k] + " " + nameMethodsSproc[k] + "(";
                    str += GenerateHeaderMethodByValuesBySproc(k);
                    str += ")";
                    str += Gobal.NewLinesAndTabs(1, tabs) + "{";
                    switch (typeGenerate)
                    {
                        case "not layer":
                            break;
                        case "forever libary":
                            str += Gobal.NewLinesAndTabs(1, tabs + 1) + "SqlServerDataProvider provider = new SqlServerDataProvider();";
                            str += Gobal.NewLinesAndTabs(1, tabs + 1) + "string sql = \"" + namesproc.Rows[k]["SPECIFIC_NAME"] + "\";";
                            if (returnSproc[k].Equals("List<string>"))
                            {

                            }
                            else
                            {
                                str += Gobal.NewLinesAndTabs(1, tabs + 1) + "return provider." + (returnSproc[k].Equals("bool") ? "ExecuteNonQuery" : "GetRecordSet") + "(sql,";
                                //str += Gobal.NewLinesAndTabs(1, tabs+!) + "DataTable dt = provider." + (returnSproc[k].Equals("bool") ? "ExecuteNonQuery" : "GetRecordSet") + "(sql,";
                                if (dtParamester[k].Rows.Count > 0)
                                {
                                    str += " new SqlServerDataProvider.DatabaseParamCls[] { ";
                                    for (int i = 0; i < dtParamester[k].Rows.Count - 1; i++)
                                    {
                                        str += Gobal.NewLinesAndTabs(1, tabs + 2) + " new SqlServerDataProvider.DatabaseParamCls(\"" + dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString() + "\", " + getParamesterName(dtParamester[k].Rows[i]["PARAMETER_NAME"].ToString()) + "),";
                                    } str += Gobal.NewLinesAndTabs(1, tabs + 2) + " new SqlServerDataProvider.DatabaseParamCls(\"" + dtParamester[k].Rows[dtParamester[k].Rows.Count - 1]["PARAMETER_NAME"].ToString() + "\", " + getParamesterName(dtParamester[k].Rows[dtParamester[k].Rows.Count - 1]["PARAMETER_NAME"].ToString()) + ")";

                                    str += Gobal.NewLinesAndTabs(1, tabs + 1) + "}, ";
                                }
                                str += " CommandType.StoredProcedure);";
                            }
                            break;
                    }
                    str += Gobal.NewLinesAndTabs(1, tabs) + "}";
                    break;
                case "Sproc":
                    str += namesproc.Rows[k]["ROUTINE_DEFINITION"].ToString();
                    str += Gobal.NewLinesAndTabs(0, tabs) + "GO";
                    break;
            }
            str += Gobal.NewLinesAndTabs(1, 0);
            return str;
        }
        #endregion
    }
    public class Gobal
    {
        private static SqlConnection con;

        public static DataTable SelectDatabaseNames()
        {
            string sql = "SELECT database_id,NAME FROM [sys].[databases] WHERE NAME NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')";
            return ExecuteDataTable(sql, "master");
        }
        public static DataTable SelectTableNames(string db)
        {
            string sql = "SELECT [TABLE_CATALOG] ,[TABLE_SCHEMA] ,[TABLE_NAME],[TABLE_TYPE] FROM [INFORMATION_SCHEMA].[TABLES] WHERE TABLE_TYPE='BASE TABLE' and [TABLE_CATALOG]='" + db + "' and [TABLE_NAME]!='sysdiagrams' ORDER BY  [TABLE_NAME] asc";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectViewNames(string db)
        {
            string sql = "SELECT [TABLE_CATALOG] ,[TABLE_SCHEMA] ,[TABLE_NAME],[TABLE_TYPE] FROM [INFORMATION_SCHEMA].[TABLES] WHERE TABLE_TYPE='VIEW' and [TABLE_CATALOG]='" + db + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectSprocNames(string db)
        {
            string sql = "SELECT [SPECIFIC_CATALOG],[SPECIFIC_SCHEMA],[SPECIFIC_NAME],[ROUTINE_CATALOG],[ROUTINE_SCHEMA],[ROUTINE_NAME],[ROUTINE_TYPE]"
                 + " ,[ROUTINE_BODY],[ROUTINE_DEFINITION],[IS_DETERMINISTIC],[SQL_DATA_ACCESS],[CREATED],[LAST_ALTERED] FROM [INFORMATION_SCHEMA].[ROUTINES]"
            + " WHERE [SPECIFIC_CATALOG]='" + db + "' " +
             "AND ROUTINE_TYPE='PROCEDURE' and SPECIFIC_NAME NOT IN ('sp_upgraddiagrams','sp_helpdiagrams','sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram','sp_alterdiagram','sp_dropdiagram','fn_diagramobjects') ORDER BY  [SPECIFIC_NAME] asc";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectSprocNameInfo(string db, string sprocname)
        {
            string sql = "SELECT [SPECIFIC_CATALOG],[SPECIFIC_SCHEMA],[SPECIFIC_NAME],[ROUTINE_CATALOG],[ROUTINE_SCHEMA],[ROUTINE_NAME],[ROUTINE_TYPE]"
                 + " ,[ROUTINE_BODY],[ROUTINE_DEFINITION],[IS_DETERMINISTIC],[SQL_DATA_ACCESS],[CREATED],[LAST_ALTERED] FROM [INFORMATION_SCHEMA].[ROUTINES]"
            + " WHERE [SPECIFIC_CATALOG]='" + db + "' and ROUTINE_NAME='" + sprocname + "' " +
             "AND ROUTINE_TYPE='PROCEDURE' and SPECIFIC_NAME NOT IN ('sp_upgraddiagrams','sp_helpdiagrams','sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram','sp_alterdiagram','sp_dropdiagram','fn_diagramobjects') ORDER BY  [SPECIFIC_NAME] asc";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectSprocParamesterNames(string db, string sprocname)
        {
            string sql = "SELECT [SPECIFIC_NAME], [PARAMETER_MODE], [IS_RESULT],[AS_LOCATOR],	 [PARAMETER_NAME],[DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH] ,CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='" + sprocname + "' ORDER BY [PARAMETER_NAME] asc";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectFuncNames(string db)
        {
            string sql = "SELECT [SPECIFIC_CATALOG],[SPECIFIC_SCHEMA],[SPECIFIC_NAME],[ROUTINE_CATALOG],[ROUTINE_SCHEMA],[ROUTINE_NAME],[ROUTINE_TYPE]"
                    + " ,[ROUTINE_BODY],[ROUTINE_DEFINITION],[IS_DETERMINISTIC],[SQL_DATA_ACCESS],[CREATED],[LAST_ALTERED] FROM [INFORMATION_SCHEMA].[ROUTINES]"
               + "  WHERE [SPECIFIC_CATALOG]='" + db + "' " +
                "AND ROUTINE_TYPE='FUNCTION' and SPECIFIC_NAME NOT IN ('sp_upgraddiagrams','sp_helpdiagrams','sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram','sp_alterdiagram','sp_dropdiagram','fn_diagramobjects')";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnNames(string db, string tblName)
        {
            string sql = "SELECT [COLUMN_NAME],[ORDINAL_POSITION],[COLUMN_DEFAULT],[IS_NULLABLE],[DATA_TYPE],CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  [CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH],[CHARACTER_SET_NAME] " +
                         " FROM  [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnNames_customer(string db, string tblName)
        {
            string sql = "SELECT [COLUMN_NAME],[COLUMN_NAME]+' ('+[DATA_TYPE]+CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL THEN '' ELSE '('+ CAST([CHARACTER_MAXIMUM_LENGTH] AS NVARCHAR)+')' END +', ' + CASE WHEN [COLUMN_DEFAULT] IS not NULL THEN CAST([COLUMN_DEFAULT] AS NVARCHAR) ElSE '' END +' '+CASE WHEN CAST([IS_NULLABLE] AS NVARCHAR) ='YES' THEN 'null' ELSE 'Not null' END +')' AS [COLUMN]" +
                         " FROM[INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='" + tblName + "' ORDER BY [ORDINAL_POSITION] asc";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnsByConstraintCheck(string db, string tblName)
        {
            string sql = "SELECT cons.CONSTRAINT_NAME,cons.TABLE_NAME, cons.TABLE_SCHEMA ,cons.CONSTRAINT_TYPE, chk.CHECK_CLAUSE"
     + " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS chk"
     + " ON chk.CONSTRAINT_NAME=cons.CONSTRAINT_NAME"
      + " WHERE CONSTRAINT_TYPE='CHECK' and TABLE_NAME='" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnsByConstraintType(string db, string tblName, string typeConstraint)
        {
            string sql = "SELECT clm.COLUMN_NAME,clm.[ORDINAL_POSITION],clm.[COLUMN_DEFAULT],clm.[IS_NULLABLE],clm.[DATA_TYPE], CASE WHEN clm.[CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  clm.[CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH],clm.[CHARACTER_SET_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] clm " +
                         " where clm.COLUMN_NAME IN(SELECT keyclm.COLUMN_NAME FROM  information_schema.[TABLE_CONSTRAINTS] cons inner JOIN  [INFORMATION_SCHEMA].key_column_usage keyclm  ON cons.[CONSTRAINT_NAME]=keyclm.[CONSTRAINT_NAME]  where cons.TABLE_NAME='" + tblName + "'";

            if (!typeConstraint.Equals(""))
            {
                sql += " AND cons.CONSTRAINT_TYPE ='" + typeConstraint + "'";
            }
            sql += ") and clm.TABLE_NAME='" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        //jjjjj
        public static DataTable SelectColumnsByNotConstraintType(string db, string tblName, List<string> typeConstraint)
        {
            string sql = "SELECT clm.COLUMN_NAME,clm.[ORDINAL_POSITION],clm.[COLUMN_DEFAULT],clm.[IS_NULLABLE],clm.[DATA_TYPE],CASE WHEN clm.[CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  clm.[CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH] ,clm.[CHARACTER_SET_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] clm " +
                         " where clm.COLUMN_NAME IN(SELECT keyclm.COLUMN_NAME FROM  information_schema.[TABLE_CONSTRAINTS] cons inner JOIN  [INFORMATION_SCHEMA].key_column_usage keyclm  ON cons.[CONSTRAINT_NAME]=keyclm.[CONSTRAINT_NAME]  where cons.TABLE_NAME='" + tblName + "'";
            for (int i = 0; i < typeConstraint.Count - 1; i++)
            {
                sql += " AND cons.CONSTRAINT_TYPE !='" + typeConstraint + "'";
            }
            sql += ") and clm.COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and TABLE_NAME='" + tblName + "') " +
               " and clm.TABLE_NAME='" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnByNotColumnIdentitys(string db, string tblName)
        {
            string sql =
                "SELECT clm.COLUMN_NAME,clm.[ORDINAL_POSITION],clm.[COLUMN_DEFAULT],clm.[IS_NULLABLE],clm.[DATA_TYPE],CASE WHEN clm.[CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  clm.[CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH],clm.[CHARACTER_SET_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] clm " +
                " where clm.COLUMN_NAME not IN (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and TABLE_NAME='" +
                tblName + "'";
            sql += " ) and clm.TABLE_NAME='" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnsByDefaultColumn(string db, string tblName)
        {
            string sql = "SELECT clm.COLUMN_NAME,clm.[ORDINAL_POSITION],clm.[COLUMN_DEFAULT],clm.[IS_NULLABLE],clm.[DATA_TYPE],CASE WHEN clm.[CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  clm.[CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH],clm.[CHARACTER_SET_NAME]" +
                         " FROM [INFORMATION_SCHEMA].[COLUMNS] clm   WHERE [TABLE_NAME]='" + tblName + "' and clm.COLUMN_NAME in " +
                         "(SELECt all_columns.name FROM sys.all_columns INNER JOIN sys.tables ON all_columns.object_id = tables.object_id" +
                         " INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id " +
                         " INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id " +
                         "WHERE schemas.name = 'dbo' AND tables.name = '" + tblName + "')";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectDefaultColumns(string db, string tblName)
        {
            string sql = "SELECt all_columns.name as columns,default_constraints.name as conscolumn FROM sys.all_columns INNER JOIN sys.tables ON all_columns.object_id = tables.object_id" +
            " INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id " +
            " INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id " +
            " WHERE schemas.name = 'dbo' AND tables.name = '" + tblName + "'";
            return ExecuteDataTable(sql, db);
        }
        public static DataTable SelectColumnNamesNotRows(string db, string tblName)
        {
            string sql = "SELECT [COLUMN_NAME],[ORDINAL_POSITION],[COLUMN_DEFAULT],[IS_NULLABLE],[DATA_TYPE],CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL then '' ELSE  [CHARACTER_MAXIMUM_LENGTH] end as [CHARACTER_MAXIMUM_LENGTH],[CHARACTER_SET_NAME] FROM[INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='" + tblName + "' and 0=1";
            return ExecuteDataTable(sql, db);
        }

        static void close()
        {
            if (con != null || con.State != ConnectionState.Closed)
            {
                con.Close();
                con = null;
            }
        }
        // form sqldatatype convert to C# datatype
        public static string ToClrType(string sqlType)
        {
            switch (sqlType.ToLower())
            {
                case "bigint":
                    return "long";

                case "binary":
                case "image":
                case "timestamp":
                case "varbinary":
                    return "byte[]";

                case "bit":
                    return "bool";

                case "char":
                case "nchar":
                case "ntext":
                case "nvarchar":
                case "text":
                case "varchar":
                case "xml":
                    return "string";

                case "datetime":
                case "smalldatetime":
                case "date":
                case "time":
                case "datetime2":
                    return "DateTime";

                case "decimal":
                case "money":
                case "smallmoney":
                    return "decimal";

                case "float":
                    return "double";

                case "int":
                    return "int";

                case "real":
                    return "float";

                case "uniqueidentifier":
                    return "Guid";

                case "smallint":
                    return "short";

                case "tinyint":
                    return "byte";

                case "variant":
                case "Udt":
                    return "object";

                case "structured":
                    return "DataTable";

                case "datetimeoffset":
                    return "DateTimeOffset";

                default:
                    return "string";
            }
        }
        public int ExecuteNonQuery(string strQuery, string db)
        {
            SqlCommand cmd = null;
            int affectedRows = 0;
            con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
            try
            {
                con.Open();
                con.ChangeDatabase(db);
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strQuery;
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                close();

            }
            return affectedRows;
        }
        public static DataTable ExecuteDataTable(string strQuery, string db)
        {
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            DataTable dt = new DataTable();
            con = new SqlConnection(ConfigurationSettings.AppSettings["connect"]);
            try
            {
                con.Open();
                con.ChangeDatabase(db);
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = strQuery;
                cmd.CommandType = CommandType.Text;
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                close();

            }
            return dt;
        }
        public static string NewLinesAndTabs(int lines, int tabs)
        {
            string str = "";
            for (int i = 0; i < lines; i++)
            {
                str += "\r\n";
            }
            for (int i = 0; i < tabs; i++)
            {
                str += "\t";
            }
            return str;
        }
        public static string UplowerString(string str)
        {
            return str.Substring(0, 1).ToUpper() + str.Substring(1);
        }

        public static string getDate()
        {
            return DateTime.Now.ToString();
        }
        public static void writeFile(string path, string nd)
        {
            using (StreamWriter writer = new StreamWriter(path, false, Encoding.UTF8))
            {
                writer.WriteLine(nd);
            }
        }
        public static void saveFileDialog(string filename, string nd)
        {
            try
            {
                using (SaveFileDialog saveFile = new SaveFileDialog())
                {
                    saveFile.FileName = filename;
                    saveFile.Title = "Save File";
                    saveFile.RestoreDirectory = true;
                    if (saveFile.ShowDialog() == DialogResult.OK)
                    {
                        writeFile(saveFile.FileName, nd);
                    } MessageBox.Show("Create file completed successfully", "Message", MessageBoxButtons.OK,
                                  MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

    }
}
